{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6d20ea4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6058ffa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_score = pd.DataFrame([[80,75,90,82],[60,85,80,92],[73,78,96,89]],\n",
    "                       index=['张三','李四','王五'],columns=['语文','数学','英语','体育'])\n",
    "df_gdp = pd.DataFrame({'city':['上海','上海','上海','北京','北京','深圳','深圳'],\n",
    "       'year':[2016,2017,2018,2017,2018,2017,2018],\n",
    "       'GDP':[27466,30133,32679,28000,30320,22286,24691],\n",
    "       'Pop':[2425,2415,2418,2171,2175,1077,1253]}, index=('华东1','华东2','华东3','华北1','华北2','华南1','华南2'))\n",
    "df_cjd =  pd.read_excel(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\data\\成绩单.xlsx\",index_col='学号').iloc[::2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a33a6b9",
   "metadata": {},
   "source": [
    "## 1. read_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "952f4981",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_csv =  pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_info.csv\",\n",
    "                         sep = ',' ,header = None, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32d0b61e",
   "metadata": {},
   "source": [
    "### 1.1 header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "14fc5f4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_order1的前2行3列数据为：\n",
      "\n",
      "          0       1                 2     3\n",
      "0  info_id  emp_id  number_consumers  mode\n",
      "1      417    1442                 4   NaN\n",
      "2      301    1095                 3   NaN\n"
     ]
    }
   ],
   "source": [
    "# df_csv =  pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_info.csv\",\n",
    "#                          sep = ',' ,header = None, encoding = 'utf-8')\n",
    "print('df_order1的前2行3列数据为：\\n\\n', df_csv.iloc[0:3,0:4])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12b88b78",
   "metadata": {},
   "source": [
    "### 1.2 index_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5a84c473",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1)df_detail1前3行4列:\n",
      "    detail_id  order_id  dishes_id  logicprn_name\n",
      "0       2956       417     610062            NaN\n",
      "1       2958       417     609957            NaN\n",
      "2       2961       417     609950            NaN\n",
      "\n",
      "(2)df_detail2前3行4列:\n",
      "            order_id  dishes_id  logicprn_name  parent_class_name\n",
      "detail_id                                                       \n",
      "2956            417     610062            NaN                NaN\n",
      "2958            417     609957            NaN                NaN\n",
      "2961            417     609950            NaN                NaN\n"
     ]
    }
   ],
   "source": [
    "# header取默认值infer，给定index_col具体参数\n",
    "df_detail1 = pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_detail1.csv\",\n",
    "                      sep = ',',encoding = 'utf-8')\n",
    "df_detail1_part=df_detail1.iloc[0:3,0:4]\n",
    "print('(1)df_detail1前3行4列:\\n',df_detail1_part )\n",
    "\n",
    "#指定index_col = 0，表示以CSV文件0列内容作为行标签\n",
    "df_detail2 = pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_detail1.csv\",\n",
    "                      sep = ',',index_col =0,encoding = 'utf-8')\n",
    "df_detail2_part=df_detail2.iloc[0:3,0:4]\n",
    "print('\\n(2)df_detail2前3行4列:\\n', df_detail2_part )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d756ab3b",
   "metadata": {},
   "source": [
    "### 1.3 usecols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "704a7d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_detail4 = pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_detail1.csv\",usecols=[1,3,4],encoding = 'utf-8')\n",
    "df_detail4_part=df_detail4.iloc[0:3,:]\n",
    "print('(1)df_detail4前3行1,3,4列:\\n', df_detail4_part )\n",
    "\n",
    "df_detail5 = pd.read_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\meal_order_detail1.csv\",usecols=['detail_id','order_id'],encoding = 'utf-8')\n",
    "df_detail5_part=df_detail5.iloc[0:3,:]\n",
    "print('\\n(2)df_detail5前3行detail_id,order_id列:\\n', df_detail5_part )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a1f39a9",
   "metadata": {},
   "source": [
    "## 2 to_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b92039a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_detail1_part.to_csv(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\pandas\\写入.csv\",sep = ';',index = False) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcf55952",
   "metadata": {},
   "source": [
    "## 3. 对于excel"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93d12726",
   "metadata": {},
   "source": [
    "使用pd.read_excel读取Excel文件  \n",
    "后缀是xlsx的Excel文件  \n",
    "不用指定sep符，会自动识别  \n",
    "df_order1.to_excel()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "993d0eb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>学号</th>\n",
       "      <th>性别</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19ACCA1</td>\n",
       "      <td>190110840128</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19ACCA1</td>\n",
       "      <td>190110840137</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19ACCA2</td>\n",
       "      <td>190110840145</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        班级            学号 性别\n",
       "0  19ACCA1  190110840128  男\n",
       "1  19ACCA1  190110840137  女\n",
       "2  19ACCA2  190110840145  女"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = pd.read_excel(r\"C:\\Users\\zhengyang\\learning\\Python\\src\\data\\成绩单.xlsx\", usecols='A:C')\n",
    "a.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e638339",
   "metadata": {},
   "source": [
    "## 4. Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08c0cae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sr_gdp_a2 = pd.Series([32679,30320,24691,23000,20363],index=range(1,10,2))\n",
    "print('sr_gdp_a2:\\n',sr_gdp_a2)\n",
    "sr_gdp_c = pd.Series([32679,30320,24691,23000,20363],\n",
    "                    index=[['华东','华北','华南','华1','西南'],['上海','北京','深圳','广州','重庆']])\n",
    "print('sr_gdp_c:\\n',sr_gdp_c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41a4e522",
   "metadata": {},
   "outputs": [],
   "source": [
    "sr_gdp_c.name=\"GDP\"\n",
    "print(sr_gdp_c)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2991a19f",
   "metadata": {},
   "source": [
    "## 5. 创建df\n",
    "### 5.1 像csv文件一样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bbd889e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_score = pd.DataFrame([[80,75,90,82],[60,85,80,92],[73,78,96,89]],\n",
    "                       index=['张三','李四','王五'],columns=['语文','数学','英语','体育'])\n",
    "df_score"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74640d04",
   "metadata": {},
   "source": [
    "### 5.2 字典式创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdce06be",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_citygdp = {'city':['上海','上海','上海','北京','北京','深圳','深圳'],\n",
    "       'year':[2016,2017,2018,2017,2018,2017,2018],\n",
    "       'GDP':[27466,30133,32679,28000,30320,22286,24691],\n",
    "       'Pop':[2425,2415,2418,2171,2175,1077,1253]}\n",
    "df_gdp = pd.DataFrame(dict_citygdp, index=('华东1','华东2','华东3','华北1','华北2','华南1','华南2'))\n",
    "df_gdp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0096eea",
   "metadata": {},
   "source": [
    "## 6. df属性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce1f8b9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_score.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b06a425",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gdp.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a51b4d06",
   "metadata": {},
   "source": [
    "## 7. 切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "456d2627",
   "metadata": {},
   "outputs": [],
   "source": [
    "# iloc 基于整数\n",
    "sr_gdp=df_gdp.iloc[1]\n",
    "sr_gdp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e1e1643",
   "metadata": {},
   "outputs": [],
   "source": [
    "# loc 切片\n",
    "df_gdp.loc[df_gdp['GDP']>25000,:]\n",
    "# iloc 这里必须list一下，否则会报错\n",
    "df_gdp.iloc[list(df_gdp['GDP']>25000),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98d3beea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 末位取得到\n",
    "df_gdp.loc['华东2':'华北2']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05f24fe7",
   "metadata": {},
   "source": [
    "## 8. 增删"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ee081dce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_gdp2 = df_gdp.copy().head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ed0a23b",
   "metadata": {},
   "source": [
    "### 8.1 增加行,concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4c8cec0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "a = df_gdp2.iloc[1].copy()\n",
    "a['GDP'] = 1000000000000000\n",
    "a.name = '333'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0f729080",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>year</th>\n",
       "      <th>GDP</th>\n",
       "      <th>Pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>2016</td>\n",
       "      <td>27466</td>\n",
       "      <td>2425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>上海</td>\n",
       "      <td>2017</td>\n",
       "      <td>30133</td>\n",
       "      <td>2415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>上海</td>\n",
       "      <td>2018</td>\n",
       "      <td>32679</td>\n",
       "      <td>2418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>上海</td>\n",
       "      <td>2017</td>\n",
       "      <td>1000000000000000</td>\n",
       "      <td>2415</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  year               GDP   Pop\n",
       "0   上海  2016             27466  2425\n",
       "1   上海  2017             30133  2415\n",
       "2   上海  2018             32679  2418\n",
       "3   上海  2017  1000000000000000  2415"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gdp2 = pd.concat([df_gdp2, a.to_frame().T], ignore_index=True)\n",
    "df_gdp2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "377dccde",
   "metadata": {},
   "source": [
    "### 8.2 增加列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fd92197d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>year</th>\n",
       "      <th>GDP</th>\n",
       "      <th>Pop</th>\n",
       "      <th>日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>2016</td>\n",
       "      <td>27466</td>\n",
       "      <td>2425</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>上海</td>\n",
       "      <td>2017</td>\n",
       "      <td>30133</td>\n",
       "      <td>2415</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>上海</td>\n",
       "      <td>2018</td>\n",
       "      <td>32679</td>\n",
       "      <td>2418</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>上海</td>\n",
       "      <td>2017</td>\n",
       "      <td>1000000000000000</td>\n",
       "      <td>2415</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  year               GDP   Pop  日期\n",
       "0   上海  2016             27466  2425   1\n",
       "1   上海  2017             30133  2415   2\n",
       "2   上海  2018             32679  2418   3\n",
       "3   上海  2017  1000000000000000  2415   4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gdp2['日期'] = [1,2,3,4]\n",
    "df_gdp2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3e54390",
   "metadata": {},
   "source": [
    "### 8.3 删除,drop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a04b49b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# inplace = True表示改变原数据，False表示不改变原数据\n",
    "# axis = 0 删除行\n",
    "df_gdp2.drop(labels = '日期',axis = 1,inplace = True)\n",
    "df_gdp2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dac4f861",
   "metadata": {},
   "source": [
    "## 9. 统计，describe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0e6afb8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>city</th>\n",
       "      <th>year</th>\n",
       "      <th>GDP</th>\n",
       "      <th>Pop</th>\n",
       "      <th>气候</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>华东1</th>\n",
       "      <td>上海</td>\n",
       "      <td>2016</td>\n",
       "      <td>27466</td>\n",
       "      <td>2425</td>\n",
       "      <td>热</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华东2</th>\n",
       "      <td>上海</td>\n",
       "      <td>2017</td>\n",
       "      <td>30133</td>\n",
       "      <td>2415</td>\n",
       "      <td>冷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华东3</th>\n",
       "      <td>上海</td>\n",
       "      <td>2018</td>\n",
       "      <td>32679</td>\n",
       "      <td>2418</td>\n",
       "      <td>冷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华北1</th>\n",
       "      <td>北京</td>\n",
       "      <td>2017</td>\n",
       "      <td>28000</td>\n",
       "      <td>2171</td>\n",
       "      <td>冷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华北2</th>\n",
       "      <td>北京</td>\n",
       "      <td>2018</td>\n",
       "      <td>30320</td>\n",
       "      <td>2175</td>\n",
       "      <td>冷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华南1</th>\n",
       "      <td>深圳</td>\n",
       "      <td>2017</td>\n",
       "      <td>22286</td>\n",
       "      <td>1077</td>\n",
       "      <td>冷</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>华南2</th>\n",
       "      <td>深圳</td>\n",
       "      <td>2018</td>\n",
       "      <td>24691</td>\n",
       "      <td>1253</td>\n",
       "      <td>热</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    city  year    GDP   Pop 气候\n",
       "华东1   上海  2016  27466  2425  热\n",
       "华东2   上海  2017  30133  2415  冷\n",
       "华东3   上海  2018  32679  2418  冷\n",
       "华北1   北京  2017  28000  2171  冷\n",
       "华北2   北京  2018  30320  2175  冷\n",
       "华南1   深圳  2017  22286  1077  冷\n",
       "华南2   深圳  2018  24691  1253  热"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gdp3 = df_gdp.copy()\n",
    "df_gdp3['气候']=['热', '冷', '冷', '冷', '冷', '冷', '热']\n",
    "df_gdp3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8f06e370",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>GDP</th>\n",
       "      <th>Pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2017.285714</td>\n",
       "      <td>27939.285714</td>\n",
       "      <td>1990.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.755929</td>\n",
       "      <td>3551.613704</td>\n",
       "      <td>576.881807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2016.000000</td>\n",
       "      <td>22286.000000</td>\n",
       "      <td>1077.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2017.000000</td>\n",
       "      <td>26078.500000</td>\n",
       "      <td>1712.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2017.000000</td>\n",
       "      <td>28000.000000</td>\n",
       "      <td>2175.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>30226.500000</td>\n",
       "      <td>2416.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>32679.000000</td>\n",
       "      <td>2425.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              year           GDP          Pop\n",
       "count     7.000000      7.000000     7.000000\n",
       "mean   2017.285714  27939.285714  1990.571429\n",
       "std       0.755929   3551.613704   576.881807\n",
       "min    2016.000000  22286.000000  1077.000000\n",
       "25%    2017.000000  26078.500000  1712.000000\n",
       "50%    2017.000000  28000.000000  2175.000000\n",
       "75%    2018.000000  30226.500000  2416.500000\n",
       "max    2018.000000  32679.000000  2425.000000"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# max()\n",
    "df_gdp3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "54375f0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city    7\n",
       "year    7\n",
       "GDP     7\n",
       "Pop     7\n",
       "气候      7\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count()\n",
    "df_gdp3.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "92fa13e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "气候\n",
       "冷    5\n",
       "热    2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gdp3['气候'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c3589b2",
   "metadata": {},
   "source": [
    "## 10. 分组聚合，计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3e98a298",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfg_班级 = df_cjd.groupby(by='班级') # 按列聚合"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c3918b5",
   "metadata": {},
   "source": [
    "### 10.1 DataFrameGroupBy属性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "52821752",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级.ngroups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b913bd71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('19ACCA1',                    班级 性别   姓名  作业和测验    实验  出勤情况  期末考试\n",
      "学号                                                    \n",
      "190110840128  19ACCA1  男  裘之楠     98  93.0   100    89\n",
      "190110840205  19ACCA1  女   陈昀     99  91.0    95    94\n",
      "190110840215  19ACCA1  女   李雯     94  93.0    95    79)\n"
     ]
    }
   ],
   "source": [
    "for i in dfg_班级:\n",
    "    print(i)\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "006f2c5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>姓名</th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>19ACCA1</td>\n",
       "      <td>男</td>\n",
       "      <td>裘之楠</td>\n",
       "      <td>98</td>\n",
       "      <td>93.0</td>\n",
       "      <td>100</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840205</th>\n",
       "      <td>19ACCA1</td>\n",
       "      <td>女</td>\n",
       "      <td>陈昀</td>\n",
       "      <td>99</td>\n",
       "      <td>91.0</td>\n",
       "      <td>95</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840215</th>\n",
       "      <td>19ACCA1</td>\n",
       "      <td>女</td>\n",
       "      <td>李雯</td>\n",
       "      <td>94</td>\n",
       "      <td>93.0</td>\n",
       "      <td>95</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   班级 性别   姓名  作业和测验    实验  出勤情况  期末考试\n",
       "学号                                                    \n",
       "190110840128  19ACCA1  男  裘之楠     98  93.0   100    89\n",
       "190110840205  19ACCA1  女   陈昀     99  91.0    95    94\n",
       "190110840215  19ACCA1  女   李雯     94  93.0    95    79"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级.get_group('19ACCA1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "17697d30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "学号\n",
       "190110840128    89\n",
       "190110840205    94\n",
       "190110840215    79\n",
       "Name: 期末考试, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级.期末考试.get_group('19ACCA1')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfdc9949",
   "metadata": {},
   "source": [
    "### 10.2 与迭代器结合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fbd0f54e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " gen_x第一次迭代：\n",
      " ('19ACCA1',                    班级 性别   姓名  作业和测验    实验  出勤情况  期末考试\n",
      "学号                                                    \n",
      "190110840128  19ACCA1  男  裘之楠     98  93.0   100    89\n",
      "190110840205  19ACCA1  女   陈昀     99  91.0    95    94\n",
      "190110840215  19ACCA1  女   李雯     94  93.0    95    79)\n",
      "\n",
      " gen_x第二次迭代：\n",
      " ('19ACCA2',                    班级 性别   姓名  作业和测验    实验  出勤情况  期末考试\n",
      "学号                                                    \n",
      "190110840145  19ACCA2  女   郑琳     93  98.0   100    76\n",
      "190110840227  19ACCA2  女  唐思韵     90  91.0   100    66)\n"
     ]
    }
   ],
   "source": [
    "gen_x =dfg_班级.__iter__()# gen_x 是一个生成器类（generator）对象\n",
    "print(\"\\n gen_x第一次迭代：\\n\",next(gen_x))   \n",
    "print(\"\\n gen_x第二次迭代：\\n\",next(gen_x))     "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41dfaa65",
   "metadata": {},
   "source": [
    "### 10.3 聚合之后的操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "102a1187",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "班级\n",
       "19ACCA1    87.333333\n",
       "19ACCA2    71.000000\n",
       "19ACCA3    80.250000\n",
       "19ACCA4    67.000000\n",
       "19财会1      79.000000\n",
       "19财会2      73.666667\n",
       "Name: 期末考试, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级.期末考试.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7eb57a4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "班级\n",
       "19ACCA1    3\n",
       "19ACCA2    2\n",
       "19ACCA3    4\n",
       "19ACCA4    2\n",
       "19财会1      1\n",
       "19财会2      3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级.size()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7505078",
   "metadata": {},
   "source": [
    "### 10.4 agg方法替代聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "a0b23bd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\zhengyang\\AppData\\Local\\Temp\\ipykernel_30476\\1983471139.py:1: FutureWarning: The provided callable <function mean at 0x000001E0AF096FC0> is currently using Series.mean. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"mean\" instead.\n",
      "  df_cjd.期末考试.agg(np.mean)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "np.float64(77.26666666666667)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cjd.期末考试.agg(np.mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7af1a66a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>实验</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>sum</th>\n",
       "      <td>1391.5</td>\n",
       "      <td>1159.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>77.266667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          实验         期末考试\n",
       "sum   1391.5  1159.000000\n",
       "mean     NaN    77.266667"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cjd.agg({'实验':\"sum\",'期末考试':[\"mean\",\"sum\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b8621922",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19ACCA1</th>\n",
       "      <td>97.0</td>\n",
       "      <td>92.333333</td>\n",
       "      <td>96.666667</td>\n",
       "      <td>87.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA2</th>\n",
       "      <td>91.5</td>\n",
       "      <td>94.500000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>71.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA3</th>\n",
       "      <td>92.5</td>\n",
       "      <td>93.625000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>80.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA4</th>\n",
       "      <td>91.0</td>\n",
       "      <td>91.500000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>67.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19财会1</th>\n",
       "      <td>92.0</td>\n",
       "      <td>91.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>79.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19财会2</th>\n",
       "      <td>94.0</td>\n",
       "      <td>92.333333</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>73.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         作业和测验         实验        出勤情况       期末考试\n",
       "班级                                              \n",
       "19ACCA1   97.0  92.333333   96.666667  87.333333\n",
       "19ACCA2   91.5  94.500000  100.000000  71.000000\n",
       "19ACCA3   92.5  93.625000  100.000000  80.250000\n",
       "19ACCA4   91.0  91.500000  100.000000  67.000000\n",
       "19财会1     92.0  91.000000  100.000000  79.000000\n",
       "19财会2     94.0  92.333333  100.000000  73.666667"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfg_班级[[\"作业和测验\", \"实验\", \"出勤情况\", \"期末考试\"]].agg(\"mean\")# 等效于dfg_班级_mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef059484",
   "metadata": {},
   "source": [
    "## 11. 数据透视表,pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3738a899",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">std</th>\n",
       "      <th colspan=\"2\" halign=\"left\">max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>实验</th>\n",
       "      <th>期末考试</th>\n",
       "      <th>实验</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>班级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19ACCA1</th>\n",
       "      <td>1.154701</td>\n",
       "      <td>7.637626</td>\n",
       "      <td>93.0</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA2</th>\n",
       "      <td>4.949747</td>\n",
       "      <td>7.071068</td>\n",
       "      <td>98.0</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA3</th>\n",
       "      <td>5.528336</td>\n",
       "      <td>8.421203</td>\n",
       "      <td>98.0</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19ACCA4</th>\n",
       "      <td>0.707107</td>\n",
       "      <td>9.899495</td>\n",
       "      <td>92.0</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19财会2</th>\n",
       "      <td>0.577350</td>\n",
       "      <td>11.676187</td>\n",
       "      <td>93.0</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19财会1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.0</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              std              max     \n",
       "               实验       期末考试    实验 期末考试\n",
       "班级                                     \n",
       "19ACCA1  1.154701   7.637626  93.0   94\n",
       "19ACCA2  4.949747   7.071068  98.0   76\n",
       "19ACCA3  5.528336   8.421203  98.0   87\n",
       "19ACCA4  0.707107   9.899495  92.0   74\n",
       "19财会2    0.577350  11.676187  93.0   84\n",
       "19财会1         NaN        NaN  91.0   79"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_p2 = pd.pivot_table(df_cjd,values=['实验','期末考试'],aggfunc=[\"std\",\"max\"], index='班级')\n",
    "df_p2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02c4283f",
   "metadata": {},
   "source": [
    "## 12. 合并,merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "6f4af1f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_平时成绩单1=df_cjd[[ \"姓名\",\"作业和测验\",\"实验\",\"出勤情况\"]][0:5]\n",
    "df_平时成绩单2=df_cjd[[ \"姓名\",\"作业和测验\",\"实验\",\"出勤情况\"]][3:8]\n",
    "df_期末成绩单1=df_cjd[[ \"姓名\",\"期末考试\"]][0:5]\n",
    "df_期末成绩单2=df_cjd[[ \"姓名\",\"期末考试\"]][3:8]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cbadf0a",
   "metadata": {},
   "source": [
    "### 12.1 堆叠合并,concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "44547c51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>裘之楠</td>\n",
       "      <td>98</td>\n",
       "      <td>93.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840145</th>\n",
       "      <td>郑琳</td>\n",
       "      <td>93</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840126</th>\n",
       "      <td>钱佳琳</td>\n",
       "      <td>97</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "      <td>90</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>90</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               姓名  作业和测验    实验  出勤情况\n",
       "学号                                  \n",
       "190110840128  裘之楠     98  93.0   100\n",
       "190110840145   郑琳     93  98.0   100\n",
       "190110840126  钱佳琳     97  98.0   100\n",
       "190110950627  沈静怡     90  98.0   100\n",
       "190110840139  夏茗宜     90  91.0   100"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_平时成绩单1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "2de9fb3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840205</th>\n",
       "      <td>陈昀</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840215</th>\n",
       "      <td>李雯</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840227</th>\n",
       "      <td>唐思韵</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               姓名  期末考试\n",
       "学号                     \n",
       "190110950627  沈静怡    68\n",
       "190110840139  夏茗宜    60\n",
       "190110840205   陈昀    94\n",
       "190110840215   李雯    79\n",
       "190110840227  唐思韵    66"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_期末成绩单2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "15f7a012",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>姓名</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "      <td>90</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100</td>\n",
       "      <td>沈静怡</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>90</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100</td>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               姓名  作业和测验    实验  出勤情况   姓名  期末考试\n",
       "学号                                             \n",
       "190110950627  沈静怡     90  98.0   100  沈静怡    68\n",
       "190110840139  夏茗宜     90  91.0   100  夏茗宜    60"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_平时成绩单1,df_期末成绩单2],axis=1,join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "69dc771a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>姓名</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>裘之楠</td>\n",
       "      <td>98.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840145</th>\n",
       "      <td>郑琳</td>\n",
       "      <td>93.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840126</th>\n",
       "      <td>钱佳琳</td>\n",
       "      <td>97.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "      <td>90.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>沈静怡</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>90.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840205</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>陈昀</td>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840215</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李雯</td>\n",
       "      <td>79.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840227</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>唐思韵</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               姓名  作业和测验    实验   出勤情况   姓名  期末考试\n",
       "学号                                              \n",
       "190110840128  裘之楠   98.0  93.0  100.0  NaN   NaN\n",
       "190110840145   郑琳   93.0  98.0  100.0  NaN   NaN\n",
       "190110840126  钱佳琳   97.0  98.0  100.0  NaN   NaN\n",
       "190110950627  沈静怡   90.0  98.0  100.0  沈静怡  68.0\n",
       "190110840139  夏茗宜   90.0  91.0  100.0  夏茗宜  60.0\n",
       "190110840205  NaN    NaN   NaN    NaN   陈昀  94.0\n",
       "190110840215  NaN    NaN   NaN    NaN   李雯  79.0\n",
       "190110840227  NaN    NaN   NaN    NaN  唐思韵  66.0"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_平时成绩单1,df_期末成绩单2],axis=1,join='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "4ebc3918",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>裘之楠</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840145</th>\n",
       "      <td>郑琳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840126</th>\n",
       "      <td>钱佳琳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>沈静怡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>夏茗宜</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840205</th>\n",
       "      <td>陈昀</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840215</th>\n",
       "      <td>李雯</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840227</th>\n",
       "      <td>唐思韵</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               姓名\n",
       "学号               \n",
       "190110840128  裘之楠\n",
       "190110840145   郑琳\n",
       "190110840126  钱佳琳\n",
       "190110950627  沈静怡\n",
       "190110840139  夏茗宜\n",
       "190110950627  沈静怡\n",
       "190110840139  夏茗宜\n",
       "190110840205   陈昀\n",
       "190110840215   李雯\n",
       "190110840227  唐思韵"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_平时成绩单1,df_期末成绩单2], axis=0,join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e979ba11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>姓名</th>\n",
       "      <th>实验</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>98.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>裘之楠</td>\n",
       "      <td>93.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840145</th>\n",
       "      <td>93.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>郑琳</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840126</th>\n",
       "      <td>97.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>钱佳琳</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>90.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>沈静怡</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>90.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>91.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840128</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>裘之楠</td>\n",
       "      <td>NaN</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840145</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郑琳</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840126</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>钱佳琳</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110950627</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>沈静怡</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190110840139</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              作业和测验   出勤情况   姓名    实验  期末考试\n",
       "学号                                         \n",
       "190110840128   98.0  100.0  裘之楠  93.0   NaN\n",
       "190110840145   93.0  100.0   郑琳  98.0   NaN\n",
       "190110840126   97.0  100.0  钱佳琳  98.0   NaN\n",
       "190110950627   90.0  100.0  沈静怡  98.0   NaN\n",
       "190110840139   90.0  100.0  夏茗宜  91.0   NaN\n",
       "190110840128    NaN    NaN  裘之楠   NaN  89.0\n",
       "190110840145    NaN    NaN   郑琳   NaN  76.0\n",
       "190110840126    NaN    NaN  钱佳琳   NaN  84.0\n",
       "190110950627    NaN    NaN  沈静怡   NaN  68.0\n",
       "190110840139    NaN    NaN  夏茗宜   NaN  60.0"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_平时成绩单1,df_期末成绩单1],axis=0,join='outer',sort=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17ef25e9",
   "metadata": {},
   "source": [
    "### 12.2 主键合并,merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "35314e8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>作业和测验</th>\n",
       "      <th>实验</th>\n",
       "      <th>出勤情况</th>\n",
       "      <th>期末考试</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>沈静怡</td>\n",
       "      <td>90</td>\n",
       "      <td>98.0</td>\n",
       "      <td>100</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>夏茗宜</td>\n",
       "      <td>90</td>\n",
       "      <td>91.0</td>\n",
       "      <td>100</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  作业和测验    实验  出勤情况  期末考试\n",
       "0  沈静怡     90  98.0   100    68\n",
       "1  夏茗宜     90  91.0   100    60"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how 调节左连接，外连接。。。\n",
    "pd.merge(df_平时成绩单1,df_期末成绩单2,left_on='姓名',\n",
    "         right_on = '姓名',how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1996aa31",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mining",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
